﻿<SQLStoredProcedureSpec
	xmlns="bb_appfx_sqlstoredprocedure"
	xmlns:common="bb_appfx_commontypes" 
	ID="eac14273-c9a8-45ab-96bb-97fe7d78f33c"
	Name="Parse Combined Pledge and Designation IDs"
	Description="Parse a combined pledge and designation id into two actual guids"
	Author="Blackbaud Professional Services"
	SPName="USR_USP_PARSEFORMATTEDPLEDGEID"
	>

	<CreateProcedureSQL>
		<![CDATA[
create procedure dbo.USR_USP_PARSEFORMATTEDPLEDGEID(
  @FormattedPledgeDesignationCombo nvarchar(255)
 ,@PledgeID uniqueidentifier = null output
 ,@DesignationID uniqueidentifier = null output
)
as
begin
  set nocount on;

  --Fix @FormattedPledgeDesignationCombo
  --it will look like one of these:
  --BBC_3365338F-40A5-4798-A87E-DCC397E0D693|C3BA6602-1620-4FF8-8C42-BE6CB832BC57
  --3365338F-40A5-4798-A87E-DCC397E0D693|C3BA6602-1620-4FF8-8C42-BE6CB832BC57
  --WV_3365338F-40A5-4798-A87E-DCC397E0D693|C3BA6602-1620-4FF8-8C42-BE6CB832BC57
  --or, more generally:
  --BBC_[pledgeid]|[designationid]
  --[pledgeid]|[designationid]
  --WV_[pledgeid]|[designationid]
  
  declare @startstring nvarchar(100) = 'BBC_';
  declare @delimiter nvarchar(100) = '|';
  declare @ignorestartstring nvarchar(3) = 'WV_';
  
  set @PledgeID = null;
  set @DesignationID = null;
  
  IF substring(@FormattedPledgeDesignationCombo,1,len(@ignorestartstring)) <> @ignorestartstring begin
    IF substring(@FormattedPledgeDesignationCombo,1,len(@startstring)) = @startstring begin
      SET @FormattedPledgeDesignationCombo = substring(@FormattedPledgeDesignationCombo,len(@startstring)+1,len(@FormattedPledgeDesignationCombo));
    END
    declare @delimiterlocation int = charindex(@delimiter,@FormattedPledgeDesignationCombo);

    --extract the pledgeid from the combined string
    declare @str_pledgeid nvarchar(max) = substring(@FormattedPledgeDesignationCombo,1,36);
    --the designation will start one character after the delimiter, and continue to the end of the combined string
    declare @str_designationid nvarchar(max) = substring(@FormattedPledgeDesignationCombo,@delimiterlocation+1,len(@FormattedPledgeDesignationCombo))

    --now convert both to proper GUIDs
    if 1 = dbo.USR_UFN_ISGUID(@str_pledgeid) begin
      set @PledgeID = convert(uniqueidentifier,@str_pledgeid);
    end;
    if 1 = dbo.USR_UFN_ISGUID(@str_designationid) begin
      set @DesignationID = convert(uniqueidentifier,@str_designationid);
    end;
  end;
end;        
]]>
	</CreateProcedureSQL>

</SQLStoredProcedureSpec>
